from xlrd import open_workbook
from xlwt import Workbook, XFStyle, Font, Alignment, Borders, Pattern


class MyExcle():
    def __init__(self):
        self.path=''
        self.sheet_name=''
        self.encoding = 'utf-8'

    def get_sheet(self):
        bk = open_workbook(self.path)
        sh = bk.sheet_by_name(self.sheet_name)
        return  sh

    def read_by_row(self,rowx, start_colx, end_colx=None):
        sh=self.get_sheet()
        row_values = sh.row_values(rowx, start_colx, end_colx)
        return row_values

    def read_by_col(self,colx, start_rowx=0, end_rowx=None):
        sh = self.get_sheet()
        col_values = sh.col_values(colx, start_rowx, end_rowx)
        return col_values

    def read_by_cell(self,rowx, colx):
        sh = self.get_sheet()
        cell_value = sh.cell_value(rowx, colx)
        return cell_value

    def read_by_list(self,start_row, end_row,start_col, den_col):
        sh = self.get_sheet()
        list = []
        for i in range(start_row, end_row):
            list.append(sh.row_values(i, start_col, den_col))
        return list

    def write_list(self, title,content,start_row,start_col):
        font=Font()
        font.bold = True

        alignment=Alignment()
        alignment.horz=alignment.HORZ_CENTER

        borders=Borders()
        borders.left = borders.THIN
        borders.right = borders.THIN
        borders.top = borders.THIN
        borders.bottom = borders.THIN
        # borders.diag = borders.THIN

        pattern=Pattern()
        pattern.pattern=pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 7

        title_style=XFStyle()
        title_style.font=font
        title_style.alignment=alignment
        title_style.borders=borders
        title_style.pattern=pattern

        content_style = XFStyle()
        content_style.alignment = alignment
        content_style.borders = borders

        wb = Workbook(self.encoding)
        sheet = wb.add_sheet(self.sheet_name)

        for i in range(len(title)):
            sheet.write(start_row, start_col+i, title[i],title_style)

        for i in range(len(content)):
            for j in range(len(content[i])):
                sheet.write(start_row+i + 1, start_col+j, content[i][j],content_style)
        wb.save(self.path)



xlr = MyExcle()
# xlr.path = r'H:\python\博思创.xls'
# xlr.sheet_name = '员工表'
#
# data = xlr.read_by_row(7,4)
# print(data)
#
# data = xlr.read_by_col(5, 6, 21)
# print(data)
#
# data=xlr.read_by_cell(7,8)
# print(data)
#
# data=xlr.read_by_list(6,21,4,13)
# print(data)
title=['部门编号', '部门名称', '部门地址']
content=[
    [1, '研发部', '三楼'],
    [2, '测试部', '顶楼'],
    [3, '运维部', '负一楼']
]
xlr.sheet_name='部门表'
xlr.path=r'H:\python\write_excle\excle8.xls'
xlr.write_list(title,content, 0, 0)